package me.zhanshi123.vipstorage.util;

import me.zhanshi123.VipSystem.VipAPI;
import me.zhanshi123.vipstorage.Main;

import java.sql.*;
import java.util.HashSet;
import java.util.Set;

public class Database {
    private static Database instance;

    public static Database getInstance() {
        return instance;
    }

    private Connection connection;

    public Database(Connection connection) {
        instance = this;
        this.connection = connection;
        init();
    }

    private PreparedStatement insert, remove, getByPlayer, getByID;

    public void init() {
        try {
            Statement statement = connection.createStatement();
            if (!connection.getMetaData().getURL().contains("database.db")) {
                statement.executeUpdate("CREATE TABLE IF NOT EXISTS `" + Main.getVipAPI().getPrefix() + "storage` (" +
                        "`id`  int UNSIGNED NOT NULL AUTO_INCREMENT ," +
                        "`player`  varchar(255) NOT NULL ," +
                        "`vip`  varchar(255) NOT NULL ," +
                        "`activate`  varchar(255) NOT NULL ," +
                        "`left`  varchar(255) NOT NULL ," +
                        "PRIMARY KEY (`id`)," +
                        "INDEX `player` (`id`, `player`) USING BTREE " +
                        ");");
            } else {
                statement.executeUpdate("CREATE TABLE IF NOT EXISTS \"main\".\"" + Main.getVipAPI().getPrefix() + "storage\" (\n" +
                        "\"id\"  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\n" +
                        "\"player\"  TEXT NOT NULL,\n" +
                        "\"vip\"  TEXT NOT NULL,\n" +
                        "\"activate\"  TEXT NOT NULL,\n" +
                        "\"left\"  TEXT NOT NULL\n" +
                        ")\n" +
                        ";");
            }
            insert = connection.prepareStatement("INSERT INTO `" + Main.getVipAPI().getPrefix() + "storage` VALUES(NULL,?,?,?,?);");
            remove = connection.prepareStatement("DELETE FROM `" + Main.getVipAPI().getPrefix() + "storage` WHERE `id` = ?;");
            getByPlayer = connection.prepareStatement("SELECT * FROM `" + Main.getVipAPI().getPrefix() + "storage` WHERE `player` = ?;");
            getByID = connection.prepareStatement("SELECT * FROM `" + Main.getVipAPI().getPrefix() + "storage` WHERE `id` = ? LIMIT 1;");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void checkConnection() {
        try {
            if (connection.isClosed()) {
                connection = VipAPI.getInstance().getConnection();
                init();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void validVerify(Set<StorageData> data) {
        new HashSet<>(data).stream().filter(entry -> !entry.isValid()).forEach(entry -> {
                    removeData(entry.getId());
                    data.remove(entry);
                }
        );
    }

    public Set<StorageData> getData(String name) {
        checkConnection();
        Set<StorageData> data = new HashSet<>();
        try {
            getByPlayer.setString(1, name);
            ResultSet rs = getByPlayer.executeQuery();
            while (rs.next()) {
                data.add(new StorageData(rs.getInt("id"), rs.getString("player"), rs.getString("vip"), rs.getLong("left"), rs.getLong("activate")));
            }
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        validVerify(data);
        return data;
    }

    public StorageData getData(int id) {
        checkConnection();
        try {
            getByID.setInt(1, id);
            ResultSet rs = getByID.executeQuery();
            StorageData data = null;
            if (rs.next()) {
                data = new StorageData(rs.getInt("id"), rs.getString("player"), rs.getString("vip"), rs.getLong("left"), rs.getLong("activate"));
            }
            rs.close();
            if (data == null) {
                return null;
            }
            if (data.isValid()) {
                return data;
            } else {
                return null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public void addData(StorageData data) {
        checkConnection();
        try {
            insert.setString(1, data.getPlayer());
            insert.setString(2, data.getVip());
            insert.setLong(3, data.getActivate());
            insert.setLong(4, data.getSeconds());
            insert.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void removeData(int id) {
        checkConnection();
        try {
            remove.setInt(1, id);
            remove.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
